/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package atm;
import java.sql.*;
import java.util.ArrayList;
import java.util.logging.Logger;
/**
 *
 * @author Ellen
 */
public class DbHandler {
    Connection c;
     ArrayList<String> att = new ArrayList();
    public boolean connect(){
        boolean connected = false;
        try{
            
            Class.forName("com.mysql.jdbc.Driver").newInstance();
            String URL = "jdbc:mysql://localhost:3306/hassesskatt?user=Hasse&password=hasse";
            c = DriverManager.getConnection(URL);
            return connected;
        }
        catch (SQLException ex){
            ex.printStackTrace();
            System.out.println("Could not connect to the db");
            disconnect();
            return connected;
        }
        catch(Exception ex){
            System.out.println("Something went wrong");
            ex.printStackTrace();
            disconnect();
            return connected;
        }
    }
    public int logIn(int passCode){
        String query = "SELECT iduser FROM user WHERE password='"+passCode+"';";
        ArrayList<String> result = executeSelect(query);
        if(result.size()<1) {
            return 0;
        }
        return Integer.parseInt(result.get(0));
    }
    public boolean withdraw(int accountId, int sum){
        String query = "SELECT sum FROM user WHERE iduser='"+accountId+"';";
        ArrayList<String> result = executeSelect(query);
        if(sum<Integer.parseInt(result.get(0)) && sum<=8000) {
            int newSum = Integer.parseInt(result.get(0))-sum;
            query="UPDATE user SET sum='"+newSum+"' WHERE iduser='"+accountId+"';";
            executeUpdate(query);
            return true;
        }
        return false;
    }
    public int checkBalance(int accountId){
            String query = "SELECT sum FROM user WHERE iduser = '"+accountId+"';";
            executeSelect(query);
            return Integer.parseInt(att.get(0));
    }
    public void disconnect(){
        try{
            c.close();
        }
        catch(SQLException ex){
            System.out.println("Could not close the connection");
        }
        catch(Exception ex){
            System.out.println("Something went wrong please contact the support");
        }
    }
     
     //metod för att göra ett SELECT-kommando
    private ArrayList<String> executeSelect(String query) {
        try {
            Class.forName("com.mysql.jdbc.Driver");
            try (Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/hassesskatt?user=Hasse&password=hasse")) {
                Statement statement = con.createStatement();
                ResultSet rs = statement.executeQuery(query);
                while (rs.next()) {
                    String a = rs.getString(1);
                    att.add(a);
                }
            }
        } catch (ClassNotFoundException | SQLException e) {
            Logger.getLogger(this.getClass().getName(), e.getMessage());
        }
        return att;
    }
    //metod för att göra INSERT. (tror även den kan användas för DELETE och UPDATE)
    private void executeUpdate(String query) {
        try {
            Class.forName("com.mysql.jdbc.Driver");
            try (Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/hassesskatt?user=Hasse&password=hasse")) {
                Statement statement = con.createStatement();
                statement.executeUpdate(query);
            }
        } catch (ClassNotFoundException | SQLException e) {
            System.err.println(e);
        }
    }
}
